package org.monkey.common.generator.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import lombok.extern.slf4j.Slf4j;
import org.monkey.common.constant.entity.DbConfig;
import org.monkey.common.constant.entity.MysqlColumnInfo;

@Slf4j
public class MysqlUtil {
  private static final String COLUMNS =
      "`TABLE_CATALOG`, `TABLE_SCHEMA`, `TABLE_NAME`, `COLUMN_NAME`, `ORDINAL_POSITION`, `COLUMN_DEFAULT`, `IS_NULLABLE`, `DATA_TYPE`, `CHARACTER_MAXIMUM_LENGTH`, `NUMERIC_PRECISION`, `NUMERIC_SCALE`, `DATETIME_PRECISION`, `CHARACTER_SET_NAME`, `COLLATION_NAME`, `COLUMN_TYPE`, `COLUMN_KEY`, `EXTRA`, `PRIVILEGES`, `COLUMN_COMMENT`, `GENERATION_EXPRESSION`";
  private static final String COLUMN_SQL_EXPRESS =
      "select "
          + COLUMNS
          + " from information_schema.columns where table_schema='%s' and table_name='%s'";
  private static final String DATABASE_QUERY = "select database()";
  /**
   * 查询mysql表字段信息 注意：此方法不会关闭mysql连接，哪怕异常，请于外部处理
   *
   * @param connection Connection mysql数据库连接
   * @param dataBaseName String 数据库名
   * @param tableName String 表名
   */
  public static List<MysqlColumnInfo> getColumns(
      Connection connection, String dataBaseName, String tableName) throws SQLException {
    String sql = String.format(COLUMN_SQL_EXPRESS, dataBaseName, tableName);
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
      ps = connection.prepareStatement(sql);
      List<MysqlColumnInfo> mcs = new ArrayList<>();
      MysqlColumnInfo mc;
      for (rs = ps.executeQuery(); rs.next(); mcs.add(mc)) {
        mc= new MysqlColumnInfo();
        mc.setTableCatalog(rs.getString(1));
        mc.setTableSchema(rs.getString(2));
        mc.setTableName(rs.getString(3));
        mc.setColumnName(rs.getString(4));
        mc.setOrdinalPosition(rs.getLong(5));
        mc.setColumnDefault(rs.getString(6));
        mc.setIsNullable(rs.getString(7));
        mc.setDataType(rs.getString(8));
        mc.setCharacterMaximumLength(rs.getLong(9));
        mc.setNumericPrecision(rs.getLong(10));
        mc.setNumericScale(rs.getLong(11));
        mc.setDatetimePrecision(rs.getLong(12));
        mc.setCharacterSetName(rs.getString(13));
        mc.setCollationName(rs.getString(14));
        mc.setColumnType(rs.getString(15));
        mc.setColumnKey(rs.getString(16));
        mc.setExtra(rs.getString(17));
        mc.setPrivileges(rs.getString(18));
        mc.setColumnComment(rs.getString(19));
        mc.setGenerationExpression(rs.getString(20));
      }
      return mcs;
    } finally {
      close(ps, rs);
    }
  }

  /**
   * 根据connection获取库名
   *
   * @param con
   * @return java.lang.String
   * @throws
   * @version V1.0.0
   * @date 2021/7/13 11:17
   */
  public static String getDateBase(Connection con) throws SQLException {
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
      String sql = "select database()";
      String dataBaseName = null;
      ps = con.prepareStatement(sql);
      rs = ps.executeQuery();
      if (rs.next()) {
        dataBaseName = rs.getString(1);
      }
      return dataBaseName;
    } finally {
      close(ps, rs);
    }
  }

  public static Connection getConnect(DbConfig dbConfig)
      throws ClassNotFoundException, SQLException {
    Class.forName(dbConfig.getDriver());
    return DriverManager.getConnection(
        dbConfig.getDatasourceUrl(), dbConfig.getUsername(), dbConfig.getPassword());
  }

  public static void close(AutoCloseable... closeables) {
    for (int i = 0; i < closeables.length; i++) {
      try {
        if (closeables[i] != null) {
          closeables[i].close();
        }
      } catch (Exception e) {
        log.info("connect close failed:", e);
      }
    }
  }
}
